package com.mmp.main.DB;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.mmp.common.DB.DBManager;
import com.mmp.main.entity.Employee;
import com.mmp.main.entity.Hospital;
import com.mmp.main.entity.HospitalDepartment;
import com.mmp.main.entity.HospitalDevice;
import com.mmp.main.entity.JobOrder;
import com.mmp.main.entity.SpInventoryContent;
import com.mmp.main.util.Enums;

@Repository
@Transactional
public class DBQueryManager extends DBManager {

	// protected DBManager dbm;
	//
	//
	//
	//
	// public DBQueryManager(DBManager dbm) {
	// this.dbm = dbm;
	// }

	public int updateUserPassword(int userId, String newPassword) {
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		String sqlString = "Update user SET password = ? WHERE id  = ?";
		map.put(1, newPassword);
		map.put(2, userId);

		return this.executeNativeQueryForUpdate(sqlString, map);

	}

	public List<HospitalDepartment> getLocationDepartments(int locationId) {
		String jpql = "SELECT h FROM HospitalDepartment h WHERE h.status = true AND h.hospital.id =  " + locationId
				+ " ORDER BY h.name";
		return this.queryJPQL(jpql, null, HospitalDepartment.class);
	}

	public HospitalDevice getDevice(int siteId, int locationId, String code, String sn, Enums.DEVICE_STATUS status,
			boolean excludeScrapped, boolean excludeTransferred) {

		boolean codeExists = code != null && !code.trim().isEmpty();
		boolean snExists = sn != null && !sn.trim().isEmpty();

		if (siteId == 0 || (!codeExists && !snExists))
			return null;

		String jpql = "SELECT h FROM HospitalDevice h WHERE h.site.id = " + siteId;

		if (locationId != 0)
			jpql += " AND h.hospital.id = " + locationId;

		if (status != null) {
			jpql += " AND h.status = " + status.getStatus();
		} else {
			if (excludeScrapped)
				jpql += " AND h.status != " + Enums.DEVICE_STATUS.SCRAPPED.getStatus();

			if (excludeTransferred)
				jpql += " AND h.status != " + Enums.DEVICE_STATUS.TRANSFERRED.getStatus();
		}

		int i = 0;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (codeExists) {
			jpql += " AND h.code = ? ";
			map.put(++i, code);
		}

		if (snExists) {
			jpql += " AND h.serialNo = ? ";
			map.put(++i, sn);
		}

		List<HospitalDevice> list = this.queryJPQL(jpql, map, HospitalDevice.class);
		if (list != null && list.size() == 1) {
			// HospitalDevice d = list.get(0);
			// return initializeAndUnproxy(d);
			return list.get(0);
			// return d;
		}

		return null;
	}

	public HospitalDevice checkDeviceCodeOrSerialDuplication(int siteId, String code, String sn, int excludedDeviceId) {

		if ((code == null || code.isEmpty()) && (sn == null || sn.isEmpty()))
			return null;

		String jpql = "select h from HospitalDevice h where h.site.id = ? and (h.code = ? or h.serialNo = ?) and h.id != ?";

		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(1, siteId);
		map.put(2, code);
		map.put(3, sn);
		map.put(4, excludedDeviceId);

		List<HospitalDevice> list = this.queryJPQL(jpql, map, HospitalDevice.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public JobOrder checkJobOrderNoDuplication(int siteId, String jobOrderNo) {

		String jpql = "SELECT j FROM JobOrder j WHERE j.hospitalDevice.site.id = " + siteId + " AND j.jobOrderNo = ?";

		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(1, jobOrderNo);

		List<JobOrder> list = this.queryJPQL(jpql, map, JobOrder.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	private List<SpInventoryContent> getSpInventoryContent(int siteId, String code, boolean underThresholdOnly) {

		Map<Integer, Object> map = new HashMap<Integer, Object>();

		String sqlString = "SELECT sp_inventory_content_id, name, MIN(NULLIf(unit_price,0)), MAX(unit_price)"
				+ " FROM sp_inventory_content INNER JOIN sp_inventory_transaction"
				+ " ON sp_inventory_content.id = sp_inventory_transaction.sp_inventory_content_id"
				+ " WHERE sp_inventory_content.site_id = ? AND sp_inventory_transaction.input = true";

		map.put(1, siteId);

		if (code != null) {
			sqlString += " AND sp_inventory_content.code = ? ";
			map.put(2, code);
		} else if (underThresholdOnly) {
			sqlString += " AND sp_inventory_content.available_quantity <= sp_inventory_content.threshold";
		}

		sqlString += " GROUP BY sp_inventory_content_id ORDER BY name";

		List<SpInventoryContent> inventoryContents = null;
		SpInventoryContent spInventoryContent = null;

		List list = this.executeNativeQueryForSearch(sqlString, map);

		if (list != null && !list.isEmpty()) {
			inventoryContents = new ArrayList<SpInventoryContent>();

			for (int i = 0; i < list.size(); i++) {

				Object[] r = (Object[]) list.get(i);

				spInventoryContent = this.findById((Integer) r[0], SpInventoryContent.class);

				if (r[2] == null)
					spInventoryContent.setMinPrice(0f);
				else
					spInventoryContent.setMinPrice(((Double) r[2]).floatValue());

				spInventoryContent.setMaxPrice((Float) r[3]);

				inventoryContents.add(spInventoryContent);
			}
		}
		return inventoryContents;
	}

	public SpInventoryContent getSpInventoryContentWithStats(int siteId, String code) {
		List<SpInventoryContent> list = getSpInventoryContent(siteId, code, false);
		if (list != null && list.size() == 1)
			return list.get(0);
		return null;
	}

	public List<SpInventoryContent> getSpInventoryContentsWithStats(int siteId, boolean underThresholdOnly) {
		return getSpInventoryContent(siteId, null, underThresholdOnly);
	}

	public List<Hospital> getSiteLocations(int siteId) {

		String jpql = "select h from Hospital h where h.status = true and h.site.id = ? order by h.healthCenter, h.name";
		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(1, siteId);
		return this.queryJPQL(jpql, map, Hospital.class);

	}

	public SpInventoryContent checkSPCategoryDuplication(int siteId, String code, int excludedSPCatId) {

		if (siteId == 0 || code == null || code.isEmpty())
			return null;

		String jpql = "select s from SpInventoryContent s where s.site.id = ? and s.code = ? and s.id != ?";

		Map<Integer, Object> map = new HashMap<Integer, Object>();
		map.put(1, siteId);
		map.put(2, code);
		map.put(3, excludedSPCatId);

		List<SpInventoryContent> list = this.queryJPQL(jpql, map, SpInventoryContent.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public JobOrder inquireJobOrder(int siteId, int locationId, String jobOrderNo) {

		String jpql = "SELECT j FROM JobOrder j WHERE j.hospitalDevice.site.id = " + siteId;

		int i = 0;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (locationId != 0) {
			jpql += " AND j.hospitalDevice.hospital.id = ?";
			map.put(++i, locationId);
		}

		if (jobOrderNo != null && !jobOrderNo.trim().isEmpty()) {
			jpql += " AND j.jobOrderNo = ?";
			map.put(++i, jobOrderNo);
		}

		List<JobOrder> list = this.queryJPQL(jpql, map, JobOrder.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public List<JobOrder> getJobOrders(int siteId, int locationId, int month, int year, Enums.JOB_ORDER_STATUS status) {
		String jpql = "SELECT j FROM JobOrder j WHERE j.hospitalDevice.site.id = " + siteId
				+ " AND j.hospitalDevice.status NOT IN (" + Enums.DEVICE_STATUS.SCRAPPED.getStatus() + ","
				+ Enums.DEVICE_STATUS.TRANSFERRED.getStatus() + ")";
		int i = 1;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (locationId != 0) {
			jpql += " AND j.hospitalDevice.hospital.id = ?";
			map.put(i++, locationId);
		}

		if (status != null) {
			if (status == Enums.JOB_ORDER_STATUS.CLOSED)
				jpql += " AND j.closed = true";
			else if (status == Enums.JOB_ORDER_STATUS.CANCELLED)
				jpql += " AND j.cancelled = true";
			else { // opened or late
				jpql += " AND j.cancelled = false AND j.closed = false";
				if (status == Enums.JOB_ORDER_STATUS.LATE)
					jpql += " AND j.jobOrderPriority.id != "
							+ Enums.JOB_ORDER_PRIORITY.NORMAL.getDBId()
							+ " AND DATEDIFF(now(),j.jobOrderDate) >= 4 AND j.secondAction IS NULL AND j.secondActionDate IS NULL";
			}
		}

		if (month != 0) {
			if (status != Enums.JOB_ORDER_STATUS.LATE) {
				if (status == null || status == Enums.JOB_ORDER_STATUS.OPENED)
					jpql += " AND MONTH(j.jobOrderDate) = ?";
				else if (status == Enums.JOB_ORDER_STATUS.CLOSED)
					jpql += " AND MONTH(j.actualCloseDate) = ?";
				else if (status == Enums.JOB_ORDER_STATUS.CANCELLED)
					jpql += " AND MONTH(j.actualCancelDate) = ?";

				map.put(i++, month);
			}
		}

		if (year != 0) {
			if (status != Enums.JOB_ORDER_STATUS.LATE) {
				if (status == null || status == Enums.JOB_ORDER_STATUS.OPENED)
					jpql += " AND YEAR(j.jobOrderDate) = ?";
				else if (status == Enums.JOB_ORDER_STATUS.CLOSED)
					jpql += " AND YEAR(j.actualCloseDate) = ?";
				else if (status == Enums.JOB_ORDER_STATUS.CANCELLED)
					jpql += " AND YEAR(j.actualCancelDate) = ?";

				map.put(i++, year);
			}
		}

		jpql += " ORDER BY j.hospitalDevice.hospital.id, j.jobOrderDate, j.hospitalDevice.hospitalDepartment.name, j.hospitalDevice.name";

		return this.queryJPQL(jpql, map, JobOrder.class);
	}

	public JobOrder searchForActiveJobOrder(int siteId, int locationId, String jobOrderNo, String deviceCode,
			String deviceSN) {

		String jpql = "SELECT j FROM JobOrder j WHERE j.hospitalDevice.site.id = " + siteId
				+ " AND j.closed = false AND j.cancelled = false AND j.hospitalDevice.status = "
				+ Enums.DEVICE_STATUS.UNDER_MAINTENANCE.getStatus();

		int i = 0;
		Map<Integer, Object> map = new HashMap<Integer, Object>();

		if (locationId != 0) {
			jpql += " AND j.hospitalDevice.hospital.id = ?";
			map.put(++i, locationId);
		}

		if (jobOrderNo != null && !jobOrderNo.trim().isEmpty()) {
			jpql += " AND j.jobOrderNo = ?";
			map.put(++i, jobOrderNo);
		}

		if (deviceCode != null && !deviceCode.trim().isEmpty()) {
			jpql += " AND j.hospitalDevice.code = ?";
			map.put(++i, deviceCode);
		}

		if (deviceSN != null && !deviceSN.trim().isEmpty()) {
			jpql += " AND j.hospitalDevice.serialNo = ?";
			map.put(++i, deviceSN);
		}

		List<JobOrder> list = this.queryJPQL(jpql, map, JobOrder.class);
		if (list != null && list.size() == 1)
			return list.get(0);

		return null;
	}

	public List<JobOrder> getDeviceJobOrders(int siteId, int locationId, String deviceCode, String deviceSN) {

		String jpql = "SELECT j FROM JobOrder j WHERE j.hospitalDevice.site.id = ? AND j.cancelled = false";

		Map<Integer, Object> map = new HashMap<Integer, Object>();
		int i = 1;

		map.put(1, siteId);

		if (locationId != 0) {
			jpql += " AND j.hospitalDevice.hospital.id = ?";
			map.put(++i, locationId);
		}

		if (deviceCode != null && !deviceCode.trim().isEmpty()) {
			jpql += " AND j.hospitalDevice.code = ?";
			map.put(++i, deviceCode);
		}

		if (deviceSN != null && !deviceSN.trim().isEmpty()) {
			jpql += " AND j.hospitalDevice.serialNo = ?";
			map.put(++i, deviceSN);
		}

		jpql += " ORDER BY j.jobOrderDate";

		return this.queryJPQL(jpql, map, JobOrder.class);
	}

	public synchronized long getJobOrderCount(int siteId, int month, int year) {
		String jpql = "SELECT COUNT(j.id) FROM JobOrder j WHERE j.hospitalDevice.site.id = " + siteId
				+ " AND MONTH(j.jobOrderDate) = " + month + " AND YEAR(j.jobOrderDate) = " + year;

		return this.getJPQLSingleResult(jpql, null, Long.class);
	}

	public List<Employee> getEmployees(int siteId, Enums.USER_TYPE[] desiredTypes, boolean includeNotActive) {
		String jpql = "SELECT e FROM Employee e WHERE e.site.id = " + siteId;
		if (!includeNotActive)
			jpql += " AND e.status = true";
		if (desiredTypes != null && desiredTypes.length > 0) {
			jpql += " AND e.userType IN ( " + desiredTypes[0].getId();
			for (int i = 1; i < desiredTypes.length; i++)
				jpql += ", " + desiredTypes[i].getId();
			jpql += ")";
		}
		jpql += " ORDER BY e.name";
		return this.queryJPQL(jpql, null, Employee.class);
	}

	public List<Employee> getEmployees(int siteId, Enums.USER_TYPE type, boolean includeNotActive) {
		return getEmployees(siteId, new Enums.USER_TYPE[] { type }, includeNotActive);
	}

	public List<Employee> getAllEmployees(int siteId, boolean includeNotActive) {
		return getEmployees(siteId, new Enums.USER_TYPE[0], includeNotActive);
	}

}
